package com.yash.rms.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.xml.sax.SAXException;



public class ExcelUtil {

	public static final DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
	
	public <T> List<T> readExcel(File file, Class<T> clazz, String mappingXML ){
		List<T> list = new ArrayList<T>();
		try {
			
			FileInputStream myInput = new FileInputStream(file);
		
			List<String> listHeader = getHeader(myInput);
			List<String> listMethods = new ArrayList<String>();
	        List<String> listTypes = new ArrayList<String>();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document dom = db.parse(getClass().getResourceAsStream("/com/files/patient_mapping.xml"));
			for(String str:listHeader) {
                Node node = dom.getElementsByTagName(str).item(0);
                if(node != null) {
                    Element ele = (Element)dom.getElementsByTagName(str).item(0);
                    String header =  node.getTextContent();
                    header = "set" + header.substring(0,1).toUpperCase() + header.substring(1);
                    listMethods.add(header);
                    listTypes.add(ele.getAttribute("dbtype"));
                } else {
                    listMethods.add("");
                    listTypes.add("");
                }
            }
			

			/** Create a POIFSFileSystem object**/
	        POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
	        /** Create a workbook using the File System**/
	        HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

	         /** Get the first sheet from workbook**/
	        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
	        Class<?>[] parameter=new Class[1];
	        int rowCount = mySheet.getPhysicalNumberOfRows();
	        int startIndex = 1;
	        for(int rowCounter = startIndex; rowCounter < rowCount; rowCounter++) {
	              T iClass = clazz.newInstance();
	              HSSFRow myRow = (HSSFRow) mySheet.getRow(rowCounter);
	              Integer colCount = 0;
	              for(String str : listMethods) {
	                  if(str.length() == 0) {                      
	                      colCount++;
	                      continue;
	                  }
	                  HSSFCell myCell = (HSSFCell) myRow.getCell(colCount);
	                  String data = "";
	                  if(myCell != null) {
	                    if(myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
	                        data = myCell.getStringCellValue();                        
	                    } else if(HSSFDateUtil.isCellDateFormatted(myCell)) {
	                        if (myCell.getDateCellValue() != null) {
	                            data = new SimpleDateFormat("MM/dd/yyyy").format(myCell.getDateCellValue());
	                        } else {
	                            data = null;
	                        }
	                    } else if(myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {                        
	                        data = String.valueOf(myCell.getNumericCellValue());                        
	                    } else if(myCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
	                        data = String.valueOf(myCell.getBooleanCellValue());
	                    }
	                  }                  
	                  String listType = listTypes.get(colCount);
	                  if("String".equals(listType)) {
	                     parameter[0] = String.class;
	                     Method thisMethod = clazz.getMethod(listMethods.get(colCount), parameter);
	                     thisMethod.invoke(iClass, data);
	                  } else if("Date".equals(listType)) {
	                      parameter[0] = Date.class;
	                      Method thisMethod = clazz.getMethod(listMethods.get(colCount), parameter);                      

	                      if(data != null && data.trim().length() > 0)
	                        thisMethod.invoke(iClass, new SimpleDateFormat("MM/dd/yyyy").parse(data));
	                      else break;
	                  } else if("Integer".equals(listType)) {
	                      parameter[0] = Integer.class;
	                      Method thisMethod = clazz.getMethod(listMethods.get(colCount), parameter);

	                      if(data.indexOf(".") != -1){
	                          data = data.substring(0, data.indexOf("."));
	                      }
	                      try{
	                        thisMethod.invoke(iClass, Integer.parseInt(data));                      
	                      }catch (NumberFormatException e){
	                      }
	                  } else if("Boolean".equals(listType)) {
	                     parameter[0] = Boolean.class;
	                     Method thisMethod = clazz.getMethod(listMethods.get(colCount), parameter);
	                     thisMethod.invoke(iClass, Boolean.valueOf(data));                      
	                  }
	                  colCount++;
	              }	          
	              
	              list.add(iClass);
	        }
			
		} catch (ParserConfigurationException e) {
			LoggerAspect.error("ParserConfigurationException", e);
		} catch (SAXException e) {
			LoggerAspect.error("SAXException", e);
		} catch (IOException e) {
			LoggerAspect.error("IOException", e);
		} catch (InstantiationException e) {
			LoggerAspect.error("InstantiationException", e);
		} catch (IllegalAccessException e) {
			LoggerAspect.error("IllegalAccessException", e);
		} catch (IllegalArgumentException e) {
			LoggerAspect.error("IllegalArgumentException", e);
		} catch (InvocationTargetException e) {
			LoggerAspect.error("InvocationTargetException", e);
		} catch (SecurityException e) {
			LoggerAspect.error("SecurityException", e);
		} catch (NoSuchMethodException e) {
			LoggerAspect.error("NoSuchMethodException", e);
		} catch (ParseException e) {
			LoggerAspect.error("ParseException", e);
		}
		
		return list;
	}

	
	public static List<String> getHeader(FileInputStream myInput) {
        int colHeaderRowIndex = 0;
        List<String> headerList = new ArrayList<String>();
        try {
            /** Create a POIFSFileSystem object**/
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            /** Create a workbook using the File System**/
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            /** Get the first sheet from workbook**/
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
            /** We now need something to iterate through the cells.**/
            HSSFRow myRow = (HSSFRow) mySheet.getRow(colHeaderRowIndex);
            Iterator<Cell> cellIter = myRow.cellIterator();
            while (cellIter.hasNext()) {
                HSSFCell myCell = (HSSFCell) cellIter.next();
                headerList.add(myCell.getStringCellValue().replaceAll(" ", ""));
            }
        } catch (Exception e) {
        	LoggerAspect.error("Exception", e);
        }
        return headerList;
    }	
}
